package com.atguigu4.Exercise;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.List;
import java.util.Scanner;

import org.junit.Test;

import com.atguigu3.bean.Student;
import com.atguigu3.util.JDBCUtils;
import com.mysql.jdbc.Connection;

public class ExerciseTest2 {

	//插入一条数据
	@Test
	public void test1()
	{
		Scanner scan = new Scanner(System.in);
		System.out.print("四级/六级：");
		int type = scan.nextInt();
		System.out.print("身份证号：");
		String IDCard = scan.next();
		System.out.print("准考证号：");
		String examCard = scan.next();
		System.out.print("学生姓名：");
		String studentName = scan.next();
		System.out.print("所在城市：");
		String location = scan.next();
		System.out.print("考试成绩：");
		int grade = scan.nextInt();
		
		String sql = "insert into examstudent(type,IDCard,examCard,studentName,location,grade) values(?,?,?,?,?,?)";
		int inserCount = update(sql,type,IDCard,examCard,studentName,location,grade);
		if(inserCount>0)
		{
			System.out.println("添加成功");
		}
		else
		{
			System.out.println("添加失败");
		}
		scan.close();
	}
	public int update(String sql,Object ...args)//sql中占位符的个数与可变形参的长度一样
	{
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnection();
			//2.预编译sql语句，返回perparedstatement的实例
			ps = (PreparedStatement) conn.prepareStatement(sql);
			//3.填充占位符
			for(int i=0;i<args.length;i++)
			{
				ps.setObject(i+1, args[i]);//小心参数声明错误
			}
			//4.执行
//			ps.execute();
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//5.关闭资源
			JDBCUtils.closeResource(conn, ps);
		}
		return 0;
	}
	
	//查询操作
	@Test
	public void test2()
	{
		System.out.println("请输入您要输入的类型：");
		System.out.println("a:准考证号");
		System.out.println("b:身份证号");
		Scanner scan = new Scanner(System.in);
		String button = scan.next();
		if("a".equals(button))
		{
			System.out.println("请输入准考证号：");
			String examCard = scan.next();
			String sql = "select FlowID,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where ExamCard = ? ";
			Student student = getInstance(Student.class,sql,examCard);
			if(student == null)
			{
				System.out.println("查无此人，请重新进入程序");
			}
			else
			{
				System.out.println(student);
			}
		}
		else if("b".equals(button))
		{
			System.out.println("请输入身份证号：");
			String IDCard = scan.next();
			String sql = "select FlowID,Type,IDCard,ExamCard,StudentName,Location,Grade from examstudent where IDCard = ? ";
			Student student = getInstance(Student.class,sql,IDCard);
			if(student == null)
			{
				System.out.println("查无此人，请重新进入程序");
			}
			else
			{
				System.out.println(student);
			}
		}
		else
		{
			System.out.println("您的输入有误！请重新进入程序。");
		}
		scan.close();
	}
	
	public <T> T getInstance(Class<T> clazz,String sql,Object ...args)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++)
			{
				ps.setObject(i+1, args[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData remd = ps.getMetaData();
			int columnCount = remd.getColumnCount();
			if(rs.next())
			{
				T t = clazz.newInstance();
				for(int i=0;i<columnCount;i++)
				{
					Object columValue = rs.getObject(i+1);
					String columnLabel = remd.getColumnLabel(i+1);
					//使用反射给每一个属性赋值：给cust对象指定的columnName属性，赋值为columValue:通过反射
					java.lang.reflect.Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}
	
	//删除操作
	@Test
	public void test3()
	{
		System.out.println("请输入学生考号：");
		Scanner scan = new Scanner(System.in);
		String examCard = scan.next();
		String slq = "delete from examstudent where examCard = ? ";
		int deleteCount = update(slq, examCard);
		if(deleteCount>0)
		{
			System.out.println("删除成功");
		}
		else
		{
			System.out.println("查无此人，请重新输入");
		}
	}
}
